﻿/// <summary>
/// Summary description for utilities
/// </summary>
[System.Web.Services.WebService(Namespace = "http://tempuri.org/")]
[System.Web.Services.WebServiceBinding(ConformsTo = System.Web.Services.WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class utilities : System.Web.Services.WebService
{
	public utilities()
	{
		//Uncomment the following line if using designed components 
		//InitializeComponent(); 
	}

	//get customers
	[System.Web.Services.WebMethod]
	public System.String WhoAmI()
	{
		// make new SQL connection
		System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(EFS.RC.CC.CH.QRDConnection.New.ConnectionString);

		// open connection
		sqlConnection.Open();

		return EFS.RC.CC.CH.QRDConnection.New.ConnectionString;
	}


	#region ConversationBrowser - Browse

	//get customers
	[System.Web.Services.WebMethod]
	public System.Xml.XmlDocument Browse_GetResults(
		System.Int32? year,
		System.Int32? month,
		System.Int32? day,
		System.Int32? endpoint_id,
		System.String agent_guid
		)
	{
		try
		{
			using (EFS.DB.Connection connection = EFS.RC.CC.CH.QRDConnection.New)
			{
				EFS.RC.ConversationBrowser.Tools.Search.Result[] items = connection.DoExecuteTableReaderQuery<EFS.RC.ConversationBrowser.Tools.Search.Result>(
					"[dbo].[EventLogs_Browse]",
					new EFS.DB.Parameters.Array(
						new System.String[] { "year", "month", "day", "endpoint_id", "agent_guid" },
						new System.Object[] { year, month, day, endpoint_id, (agent_guid.Equals("") ? null : agent_guid) }),
						SearchResult_FromTableRow,
						System.Data.CommandType.StoredProcedure);

				// create list
				EFS.RC.ConversationBrowser.Tools.Search.ResultList retItems = new EFS.RC.ConversationBrowser.Tools.Search.ResultList(items);

				// ok
				return ToXmlDocument(retItems);
			}
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex);

			// thtrow
			throw ex;
		}
	}

	[System.Web.Services.WebMethod]
	public System.Xml.XmlDocument Browse_Agents_GetAll()
	{
		try
		{
			using (EFS.DB.Connection connection = EFS.RC.CC.CH.QRDConnection.New)
			{
				using (System.Data.DataSet resultDataSet = connection.DoExecuteQuery(
					"[dbo].[EventLogs_Agents]", null, System.Data.CommandType.StoredProcedure))
				{
					// create collection
					System.Collections.Generic.List<EFS.RC.ConversationBrowser.Tools.Browse.Agent> list =
						new System.Collections.Generic.List<EFS.RC.ConversationBrowser.Tools.Browse.Agent>();

					// load data
					if (!resultDataSet.HasErrors && resultDataSet.Tables.Count > 0)
					{
						using (System.Data.DataTable table = resultDataSet.Tables[0])
						{
							foreach (System.Data.DataRow row in table.Rows)
							{
								list.Add(AgentFromRow(row));
							}
						}
					}

					// ok
					return ToXmlDocument(list);
				}
			}
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex, null);

			// throw
			throw ex;
		}
	}

	[System.Web.Services.WebMethod]
	public System.Xml.XmlDocument Browse_Years_GetAll()
	{
		try
		{
			using (EFS.DB.Connection connection = EFS.RC.CC.CH.QRDConnection.New)
			{
				using (System.Data.DataSet resultDataSet = connection.DoExecuteQuery(
					"[dbo].[EventLogs_Years]", null, System.Data.CommandType.StoredProcedure))
				{
					// create collection
					System.Collections.Generic.List<EFS.RC.ConversationBrowser.Tools.Browse.Year> list =
						new System.Collections.Generic.List<EFS.RC.ConversationBrowser.Tools.Browse.Year>();

					// load data
					if (!resultDataSet.HasErrors && resultDataSet.Tables.Count > 0)
					{
						using (System.Data.DataTable table = resultDataSet.Tables[0])
						{
							foreach (System.Data.DataRow row in table.Rows)
							{
								list.Add(YearFromRow(row));
							}
						}
					}

					// ok
					return ToXmlDocument(list);
				}
			}
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex, null);

			// throw
			throw ex;
		}
	}

	[System.Web.Services.WebMethod]
	public System.Xml.XmlDocument Browse_Endpoints_GetAll()
	{
		try
		{
			using (EFS.DB.Connection connection = EFS.RC.CC.CH.QRDConnection.New)
			{
				using (System.Data.DataSet resultDataSet = connection.DoExecuteQuery(
					"[dbo].[EventLogs_Endpoints]", null, System.Data.CommandType.StoredProcedure))
				{
					// create collection
					System.Collections.Generic.List<EFS.RC.ConversationBrowser.Tools.Browse.Endpoint> list =
						new System.Collections.Generic.List<EFS.RC.ConversationBrowser.Tools.Browse.Endpoint>();

					// load data
					if (!resultDataSet.HasErrors && resultDataSet.Tables.Count > 0)
					{
						using (System.Data.DataTable table = resultDataSet.Tables[0])
						{
							foreach (System.Data.DataRow row in table.Rows)
							{
								list.Add(EndpointFromRow(row));
							}
						}
					}

					// ok
					return ToXmlDocument(list);
				}
			}
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex, null);

			// throw
			throw ex;
		}
	}

	[System.Web.Services.WebMethod]
	public System.Xml.XmlDocument Browse_MonthsOfYear_GetAll(System.Int32 year)
	{
		try
		{
			using (EFS.DB.Connection connection = EFS.RC.CC.CH.QRDConnection.New)
			{
				using (System.Data.DataSet resultDataSet = connection.DoExecuteQuery(
					"[dbo].[EventLogs_MonthsOfYear]",
					new EFS.DB.Parameters.Array(
						new System.String[] { "year" },
						new System.Object[] { year }), System.Data.CommandType.StoredProcedure))
				{
					// create collection
					System.Collections.Generic.List<EFS.RC.ConversationBrowser.Tools.Browse.Month> list =
						new System.Collections.Generic.List<EFS.RC.ConversationBrowser.Tools.Browse.Month>();

					// load data
					if (!resultDataSet.HasErrors && resultDataSet.Tables.Count > 0)
					{
						using (System.Data.DataTable table = resultDataSet.Tables[0])
						{
							foreach (System.Data.DataRow row in table.Rows)
							{
								list.Add(MonthFromRow(row));
							}
						}
					}

					// ok
					return ToXmlDocument(list);
				}
			}
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex, null);

			// throw
			throw ex;
		}
	}

	[System.Web.Services.WebMethod]
	public System.Xml.XmlDocument Browse_DaysOfMonth_GetAll(System.Int32 year, System.Int32 month)
	{
		try
		{

			using (EFS.DB.Connection connection = EFS.RC.CC.CH.QRDConnection.New)
			{
				using (System.Data.DataSet resultDataSet = connection.DoExecuteQuery(
					"[dbo].[EventLogs_DaysOfMonth]",
					new EFS.DB.Parameters.Array(
						new System.String[] { "year", "month" },
						new System.Object[] { year, month }), System.Data.CommandType.StoredProcedure))
				{
					// create collection
					System.Collections.Generic.List<EFS.RC.ConversationBrowser.Tools.Browse.Day> list =
						new System.Collections.Generic.List<EFS.RC.ConversationBrowser.Tools.Browse.Day>();

					// load data
					if (!resultDataSet.HasErrors && resultDataSet.Tables.Count > 0)
					{
						using (System.Data.DataTable table = resultDataSet.Tables[0])
						{
							foreach (System.Data.DataRow row in table.Rows)
							{
								list.Add(DayFromRow(row));
							}
						}
					}

					// ok
					return ToXmlDocument(list);
				}
			}
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex, null);

			// throw
			throw ex;
		}
	}
	#endregion

	#region ConversationBrowser - Browse Tools

	public EFS.RC.ConversationBrowser.Tools.Browse.Agent AgentFromRow(System.Data.DataRow row)
	{
		try
		{

			EFS.RC.ConversationBrowser.Tools.Browse.Agent agent = new EFS.RC.ConversationBrowser.Tools.Browse.Agent();

			agent.FirstName = row["FIRST_NAME"].ToString();
			agent.LastName = row["LAST_NAME"].ToString();
			agent.Guid = new System.Guid(row["AGENT_AD_GUID"].ToString());

			return agent;
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex, null);

			// throw
			throw ex;
		}
	}

	public EFS.RC.ConversationBrowser.Tools.Browse.Day DayFromRow(System.Data.DataRow row)
	{
		try
		{
			EFS.RC.ConversationBrowser.Tools.Browse.Day day = new EFS.RC.ConversationBrowser.Tools.Browse.Day();

			day.Code = System.Int32.Parse(row["Day"].ToString());

			return day;
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex, null);

			// throw
			throw ex;
		}
	}

	public EFS.RC.ConversationBrowser.Tools.Browse.Endpoint EndpointFromRow(System.Data.DataRow row)
	{
		try
		{
			EFS.RC.ConversationBrowser.Tools.Browse.Endpoint endpoint = new EFS.RC.ConversationBrowser.Tools.Browse.Endpoint();

			endpoint.ID = System.Int32.Parse(row["ENDPOINT_ID"].ToString());
			endpoint.TelNumber = row["TEL"].ToString();

			return endpoint;
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex, null);

			// throw
			throw ex;
		}
	}

	public EFS.RC.ConversationBrowser.Tools.Browse.Month MonthFromRow(System.Data.DataRow row)
	{
		try
		{
			EFS.RC.ConversationBrowser.Tools.Browse.Month month = new EFS.RC.ConversationBrowser.Tools.Browse.Month();

			month.Name = row["Name"].ToString();
			month.Code = System.Int32.Parse(row["Code"].ToString());

			return month;
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex, null);

			// throw
			throw ex;
		}
	}

	public EFS.RC.ConversationBrowser.Tools.Browse.Year YearFromRow(System.Data.DataRow row)
	{
		try
		{
			EFS.RC.ConversationBrowser.Tools.Browse.Year year = new EFS.RC.ConversationBrowser.Tools.Browse.Year();

			year.Yr = System.Int32.Parse(row["Year"].ToString());

			return year;
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex, null);

			// throw
			throw ex;
		}
	}

	#endregion

	#region Misc

	/// Returns an object from a data row
	public static void SearchResult_FromTableRow(
		System.Data.DataRow row,
		ref EFS.RC.ConversationBrowser.Tools.Conversation.Event resultObj)
	{
		// check
		EFS.Common.Verify.Reference(row, "row");
		EFS.Common.Verify.Reference(resultObj, "resultObj");

		// CONVERSATION_GUID
		resultObj.ConversationGuid = EFS.DB.DataRow.Guid(row, "CONVERSATION_GUID");

		// OCCURRED
		resultObj.Occurred = EFS.DB.DataRow.DateTime(row, "OCCURRED");

		// EVENT_TYPE
		resultObj.EventCode = EFS.DB.DataRow.Int32(row, "EVENT_CODE");
		System.String sEvtType = "";
		if (resultObj.EventCode.HasValue)
		{
			sEvtType = ((EFS.RC.CC.Events.Conversation)resultObj.EventCode.Value).ToString();
		}
		resultObj.EventType = sEvtType;

		// AGENT_FIRST_NAME
		resultObj.AgentFirstName = EFS.DB.DataRow.String(row, "AGENT_FIRST_NAME");

		// AGENT_LAST_NAME
		resultObj.AgentLastName = EFS.DB.DataRow.String(row, "AGENT_LAST_NAME");

		// SUPERVISOR_FIRST_NAME
		resultObj.SupervisorFirstName = EFS.DB.DataRow.String(row, "SUPERVISOR_FIRST_NAME");

		// SUPERVISOR_LAST_NAME
		resultObj.SupervisorLastName = EFS.DB.DataRow.String(row, "SUPERVISOR_LAST_NAME");

		// CUSTOMER_URI
		resultObj.CustomerUri = EFS.DB.DataRow.String(row, "CUSTOMER_URI");

		// HW_PARTY_ID
		resultObj.HWPartyID = EFS.DB.DataRow.Int64(row, "HW_PARTY_ID");

		// HW_PARTY_ID
		resultObj.HWApplicantID = EFS.DB.DataRow.Int32(row, "HW_APPLICANT_ID");

		// FIRST_NAME
		resultObj.CustomerFirstName = EFS.DB.DataRow.String(row, "FIRST_NAME");

		// LAST_NAME
		resultObj.CustomerLastName = EFS.DB.DataRow.String(row, "LAST_NAME");

		// CONVERSATION_CLASS_NAME
		resultObj.ConversationClassName = EFS.DB.DataRow.String(row, "CONVERSATION_CLASS_NAME");
	}

	/// Returns an object from a data row
	public static EFS.RC.ConversationBrowser.Tools.Search.Result SearchResult_FromTableRow(
		System.Data.DataRow row)
	{
		// check
		EFS.Common.Verify.Reference(row, "row");

		EFS.RC.ConversationBrowser.Tools.Search.Result resultObj = new EFS.RC.ConversationBrowser.Tools.Search.Result();

		// CONVERSATION_GUID
		resultObj.ConversationGuid = EFS.DB.DataRow.Guid(row, "CONVERSATION_GUID");

		// EVENT_CODE
		resultObj.EventCode = EFS.DB.DataRow.Int32(row, "EVENT_CODE");

		// OCCURRED
		resultObj.Occurred = EFS.DB.DataRow.DateTime(row, "OCCURRED");

		// AGENT_FIRST_NAME
		resultObj.AgentFirstName = EFS.DB.DataRow.String(row, "AGENT_FIRST_NAME");

		// AGENT_LAST_NAME
		resultObj.AgentLastName = EFS.DB.DataRow.String(row, "AGENT_LAST_NAME");

		// SUPERVISOR_FIRST_NAME
		resultObj.SupervisorFirstName = EFS.DB.DataRow.String(row, "SUPERVISOR_FIRST_NAME");

		// SUPERVISOR_LAST_NAME
		resultObj.SupervisorLastName = EFS.DB.DataRow.String(row, "SUPERVISOR_LAST_NAME");

		// CUSTOMER_URI
		resultObj.CustomerUri = EFS.DB.DataRow.String(row, "CUSTOMER_URI");

		// HW_PARTY_ID
		resultObj.HWPartyID = EFS.DB.DataRow.Int64(row, "HW_PARTY_ID");

		// HW_PARTY_ID
		resultObj.HWApplicantID = EFS.DB.DataRow.Int32(row, "HW_APPLICANT_ID");

		// FIRST_NAME
		resultObj.CustomerFirstName = EFS.DB.DataRow.String(row, "FIRST_NAME");

		// LAST_NAME
		resultObj.CustomerLastName = EFS.DB.DataRow.String(row, "LAST_NAME");

		// CONVERSATION_CLASS_NAME
		resultObj.ConversationClassName = EFS.DB.DataRow.String(row, "CONVERSATION_CLASS_NAME");

		// EVENT_TYPE
		System.Int32? evtType = EFS.DB.DataRow.Int32(row, "EVENT_CODE");
		if (evtType.HasValue)
		{
			if (evtType == 0) resultObj.CallDirection = "In";
			if (evtType == 1) resultObj.CallDirection = "Out";
		}

		System.Int32? duration = EFS.DB.DataRow.Int32(row, "DURATION");
		if (duration != null)
		{
			System.Int32 min = duration.Value / 60;
			System.Int32 sec = duration.Value % 60;

			resultObj.Duration = min.ToString() + "min " + sec.ToString() + "sec";
		}
		// done
		return resultObj;
	}

	/// ToXmlDocument
	private static System.Xml.XmlDocument ToXmlDocument(
		System.Object data)
	{
		System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
		xmlDoc.LoadXml(EFS.Common.Serialization.Functions.SerializeToString(data));
		return xmlDoc;
	}
	#endregion

	#region EventLogs
	//get customers
	[System.Web.Services.WebMethod]
	public System.Xml.XmlDocument EventLogs_Search(
		System.String agentFirstname,
		System.String agentLastname,
		System.String supervisorFirstname,
		System.String supervisorLastname,
		System.DateTime? from,
		System.DateTime? to,
		System.String firstName,
		System.String lastName,
		System.String uri,
		System.Int64? hwPartyID,
		System.Int64? hwApplicantID,
		System.Int64? classificationID,
		System.Int32? duration_from_min,
		System.Int32? duration_to_min)
	{
		try
		{
			using (EFS.DB.Connection connection = EFS.RC.CC.CH.QRDConnection.New)
			{
				EFS.RC.ConversationBrowser.Tools.Search.Result[] items = connection.DoExecuteTableReaderQuery<EFS.RC.ConversationBrowser.Tools.Search.Result>(
					"[dbo].[EventLogs_Search]",
					new EFS.DB.Parameters.Array(
						new System.String[] { "agent_firstname", "agent_lastname", "supervisor_firstname", "supervisor_lastname", "from", "to", "customer_firstname", "customer_lastname", "customer_uri", "hw_party_id", "hw_applicant_id", "classification_id", "duration_from_min", "duration_to_min" },
						new System.Object[] { agentFirstname, agentLastname, supervisorFirstname, supervisorLastname, from, to, firstName, lastName, uri, hwPartyID, hwApplicantID, classificationID, duration_from_min, duration_to_min }),
						SearchResult_FromTableRow,
						System.Data.CommandType.StoredProcedure);

				// create list
				EFS.RC.ConversationBrowser.Tools.Search.ResultList retItems = new EFS.RC.ConversationBrowser.Tools.Search.ResultList(items);

				// ok
				return ToXmlDocument(retItems);
			}
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex, null);

			// throw
			throw ex;
		}
	}

	//get customers
	[System.Web.Services.WebMethod]
	public System.Xml.XmlDocument EventLogs_GetConversationEvents(
		System.Guid conversationGuid)
	{
		try
		{

			using (EFS.DB.Connection connection = EFS.RC.CC.CH.QRDConnection.New)
			{
				using (System.Data.DataSet resultDataSet = connection.DoExecuteQuery(
						"[dbo].[EventLogs_GetConversationEvents]",
						new EFS.DB.Parameters.Array(
							new System.String[] { "conversation_guid" },
							new System.Object[] { conversationGuid }),
							System.Data.CommandType.StoredProcedure))
				{
					// create collection
					System.Collections.Generic.List<EFS.RC.ConversationBrowser.Tools.Conversation.Event> list =
						new System.Collections.Generic.List<EFS.RC.ConversationBrowser.Tools.Conversation.Event>();

					// load data
					if (!resultDataSet.HasErrors && resultDataSet.Tables.Count > 0)
					{
						using (System.Data.DataTable table = resultDataSet.Tables[0])
						{
							// eventsevent
							EFS.RC.ConversationBrowser.Tools.Conversation.Event firstEventObj = null;
							EFS.RC.ConversationBrowser.Tools.Conversation.Event previousEventObj = null;

							// cicle all
							foreach (System.Data.DataRow row in table.Rows)
							{
								EFS.RC.ConversationBrowser.Tools.Conversation.Event resultObj = new EFS.RC.ConversationBrowser.Tools.Conversation.Event();
								SearchResult_FromTableRow(row, ref resultObj);

								// test prev event
								if (null == firstEventObj)
								{
									firstEventObj = resultObj;
								}

								// test prev event
								if (null != previousEventObj)
								{
									previousEventObj.Ended = resultObj.Occurred;
									previousEventObj.DurationFromBeginningTicks = (previousEventObj.Ended.Value - firstEventObj.Occurred.Value).Ticks;
								}
								previousEventObj = resultObj;

								// add
								list.Add(resultObj);
							}
						}
					}

					// create list
					EFS.RC.ConversationBrowser.Tools.Conversation.EventList retItems = new EFS.RC.ConversationBrowser.Tools.Conversation.EventList(list.ToArray());

					// ok
					return ToXmlDocument(retItems);
				}
			}
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex, null);

			// throw
			throw ex;
		}
	}

	//get all Classifications_GetAll
	[System.Web.Services.WebMethod]
	public System.Xml.XmlDocument Classifications_GetAll()
	{
		try
		{
			EFS.RC.CC.CH.Entities.Classification[] classificationsMsg = EFS.RC.CC.CH.Tables.Classifications.GetAll();
			return ToXmlDocument(classificationsMsg);
		}
		catch (System.Exception ex)
		{
			// log
			EFS.EventLog.Log.LogException(ex, null);

			// throw
			throw ex;
		}
	}
	#endregion

	#region Test

	//get all skills
	[System.Web.Services.WebMethod]
	public System.String CheckExceptionsLog()
	{
		try
		{
			throw new System.Exception("Test Exception");
		}
		catch (System.Exception ex)
		{
			try
			{
				EFS.EventLog.Log.LogException(ex, null);
			}
			catch (System.Exception)
			{
				return ex.Message;
			}
		}

		// ok
		return "Ok";
	}

	#endregion

}